package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.OrganizationDepartmentPrincipal;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TeacherPoint;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * 部门负责人详情 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-04-28
 */
public interface OrganizationDepartmentPrincipalMapper extends BaseMapper<OrganizationDepartmentPrincipal> {



    @Select({"<script>",
            "SELECT ",
            "getSchoolName(odp.school_id) AS schoolName, ",
            "od.department_name, ",
            "s.NAME AS staff_name, ",
            "SUM(CASE WHEN rd.rank_num = 1 THEN 1 ELSE 0 END) AS green_count, ",
            "SUM(CASE WHEN rd.rank_num &lt;&gt; 1 AND rd.rade_num &gt; rf.rade_num THEN 1 ELSE 0 END) AS yellow_count, ",
            "SUM(CASE WHEN rd.rade_num &lt; rf.rade_num THEN 1 ELSE 0 END) AS red_count, ",
            "odp.school_id,",
            "odp.department_id,",
            "odp.staff_id",
            "FROM ",
            "organization_department_principal odp ",
            "INNER JOIN org_duty AS t8 ON odp.duty_id = t8.id ",
            "AND t8.duty_type = 1",
            "LEFT JOIN staff s ON s.id = odp.staff_id ",
            "LEFT JOIN organization_department od ON od.id = odp.organization_id ",
            "AND od.school_id = odp.school_id ",
            "INNER JOIN rade_look_set rls ON od.department_id = rls.department_id ",
            "INNER JOIN rade_fiducial_set rfs ON rls.fiducial_set_id = rfs.id ",
            "AND rfs.status = 1 ",
            "LEFT JOIN rade_data rd ON rd.school_id = odp.school_id ",
            "AND rd.year_part = rfs.year_part ",
            "AND rd.quarter_num = rfs.quarter_num ",
            "AND rd.lesson_type = rfs.lesson_type ",
            "AND (rd.subjects_id = rfs.subjects_id) ",
            "AND rd.projects_id = rfs.projects_id ",
            "AND rd.deleted = 0 ",
            "AND rd.stat_type = 6 ",
            "AND rd.lesson_num BETWEEN odp.start_lesson_num AND odp.end_lesson_num ",
            "LEFT JOIN rade_fiducial rf ON rf.year_part = rfs.year_part ",
            "AND rf.quarter_num = rfs.quarter_num ",
            "AND rf.lesson_type = rfs.lesson_type ",
            "AND (rf.subjects_id = rfs.subjects_id) ",
            "AND rf.projects_id = rfs.projects_id ",
            "AND rf.lesson_num = rd.lesson_num ",
            "WHERE ",
            "1 = 1 ",

            "<if test='yearPart != null and yearPart !=&apos;&apos;'>",
            "AND rfs.year_part = ${yearPart} ",
            "</if> ",
            "<if test='quarterNum != null and quarterNum !=&apos;&apos;'>",
            "AND rfs.quarter_num = ${quarterNum} ",
            "</if> ",

            "<if test ='staffName != null and staffName !=&apos;&apos;'>",
            "AND s.`name`  LIKE '%${staffName}%' ",
            "</if>",

            "<if test ='schoolId != null and schoolId !=&apos;&apos;'>",
            "AND  odp.school_id = ${schoolId} ",
            "</if>",

            "<if test ='departmentId != null and departmentId !=&apos;&apos;'>",
            "AND  rls.department_id = ${departmentId} ",
            "</if>",

            "<if test ='fiducialSetId != null and fiducialSetId !=&apos;&apos;'>",
            "AND rls.fiducial_set_id =  ${fiducialSetId} ",
            "</if>",

            "AND od.status = 1 ",
            "GROUP BY ",
            "odp.id, odp.school_id, od.department_name ",
            "<if test ='type != null and type !=&apos;&apos; and isDescOrAsc != null and isDescOrAsc !=&apos;&apos;'>",
                "<if test ='type == 0'>",
                    "<if test ='isDescOrAsc == 0'>",
                    "ORDER BY green_count",
                    "</if>",
                    "<if test ='isDescOrAsc == 1'>",
                    "ORDER BY green_count DESC ",
                    "</if>",
                "</if>",
                "<if test ='type == 1'>",
                    "<if test ='isDescOrAsc == 0'>",
                    "ORDER BY yellow_count",
                    "</if>",
                    "<if test ='isDescOrAsc == 1'>",
                    "ORDER BY yellow_count DESC",
                    "</if>",
                "</if>",
                "<if test ='type == 2'>",
                    "<if test ='isDescOrAsc == 0'>",
                    "ORDER BY red_count",
                    "</if>",
                    "<if test ='isDescOrAsc == 1'>",
                    "ORDER BY red_count DESC",
                    "</if>",
                "</if>",
            "</if>",

            "</script>"})
    public IPage<OrganizationDepartmentPrincipal> findAll(IPage<OrganizationDepartmentPrincipal> page,
                                                          @Param("yearPart") String yearPart,
                                                          @Param("quarterNum") String quarterNum,
                                                          @Param("staffName") String staffName,
                                                          @Param("departmentId") String departmentId,
                                                          @Param("schoolId") String schoolId,
                                                          @Param("fiducialSetId") String fiducialSetId,
                                                          @Param("type") String type,
                                                          @Param("isDescOrAsc") String isDescOrAsc);

    @Select({"<script>",
            "SELECT ",
            "getSchoolName(odp.school_id) AS schoolName, ",
            "od.department_name, ",
            "s.NAME AS staff_name, ",
            "rd.rade_num, ",
            "rd.lesson_num, ",
            "rf.rade_num AS reference_value, ",
            "rf.projects_name ",
            "FROM ",
            "organization_department_principal odp ",
            "INNER JOIN org_duty AS t8 ON odp.duty_id = t8.id ",
            "AND t8.duty_type = 1",
            "LEFT JOIN staff s ON s.id = odp.staff_id ",
            "LEFT JOIN organization_department od ON od.id = odp.organization_id ",
            "AND od.school_id = odp.school_id ",
            "INNER JOIN rade_look_set rls ON od.department_id = rls.department_id ",
            "INNER JOIN rade_fiducial_set rfs ON rls.fiducial_set_id = rfs.id ",
            "AND rfs.status = 1 ",
            "LEFT JOIN rade_data rd ON rd.school_id = odp.school_id ",
            "AND rd.year_part = rfs.year_part ",
            "AND rd.quarter_num = rfs.quarter_num ",
            "AND rd.lesson_type = rfs.lesson_type ",
            "AND (rd.subjects_id = rfs.subjects_id) ",
            "AND rd.projects_id = rfs.projects_id ",
            "AND rd.deleted = 0 ",
            "AND rd.stat_type = 6 ",
            "AND rd.lesson_num BETWEEN odp.start_lesson_num AND odp.end_lesson_num ",
            "LEFT JOIN rade_fiducial rf ON rf.year_part = rfs.year_part ",
            "AND rf.quarter_num = rfs.quarter_num ",
            "AND rf.lesson_type = rfs.lesson_type ",
            "AND (rf.subjects_id = rfs.subjects_id) ",
            "AND rf.projects_id = rfs.projects_id ",
            "AND rf.lesson_num = rd.lesson_num ",
            "WHERE ",
            "1 = 1 ",
            "<if test='schoolId != null and schoolId !=&apos;&apos;'>",
            "AND odp.school_id = ${schoolId} ",
            "</if> ",
            "<if test='departmentId != null and departmentId !=&apos;&apos;'>",
            "AND rls.department_id = ${departmentId} ",
            "</if> ",
            "<if test ='staffId != null and staffId !=&apos;&apos;'>",
            "AND  odp.staff_id  = ${staffId} ",
            "</if>",
            "<if test ='fiducialSetId != null and fiducialSetId !=&apos;&apos;'>",
            "AND rls.fiducial_set_id = ${fiducialSetId} ",
            "</if>",

            "<if test ='lessonNum != null and lessonNum !=&apos;&apos;'>",
            "AND rf.lesson_num = ${lessonNum} ",
            "</if>",

            "AND od.status = 1 ",

            "<if test ='type != null and type !=&apos;&apos;'>",
                "<if test ='type == 0'>",
                    "AND rd.rank_num = 1 ",
                "</if>",
                "<if test ='type == 1'>",
                   "AND rd.rank_num &lt;&gt; 1 AND rd.rade_num &gt; rf.rade_num ",
                "</if>",
                "<if test ='type == 2'>",
                  "AND rd.rade_num &lt; rf.rade_num ",
                "</if>",
            "</if>",

            "</script>"})

    public IPage<OrganizationDepartmentPrincipal> details(IPage<OrganizationDepartmentPrincipal> page,
                                                          @Param("yearPart") String yearPart,
                                                          @Param("quarterNum") String quarterNum,
                                                          @Param("staffId") String staffId,
                                                          @Param("departmentId") String departmentId,
                                                          @Param("schoolId") String schoolId,
                                                          @Param("type") String type,
                                                          @Param("lessonNum") String lessonNum,
                                                          @Param("fiducialSetId") String fiducialSetId);

}
